{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step1** 读取第一个数据包,并展示10个样本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 19744 entries, 0 to 19743\n",
      "Columns: 219 entries, ID to versionID\n",
      "dtypes: category(184), float32(11), float64(20), object(4)\n",
      "memory usage: 8.1+ MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>householdID</th>\n",
       "      <th>communityID</th>\n",
       "      <th>dc001_w4</th>\n",
       "      <th>dc002_w4</th>\n",
       "      <th>dc003_w4</th>\n",
       "      <th>dc005_w4</th>\n",
       "      <th>dc006_w4</th>\n",
       "      <th>dc007_w4</th>\n",
       "      <th>dc008_w4</th>\n",
       "      <th>...</th>\n",
       "      <th>dc068_w4_s2</th>\n",
       "      <th>dc068_w4_s3</th>\n",
       "      <th>dc068_w4_s4</th>\n",
       "      <th>dc068_w4_s5</th>\n",
       "      <th>dc068_w4_s6</th>\n",
       "      <th>dc068_w4_s7</th>\n",
       "      <th>dc068_w4_s8</th>\n",
       "      <th>dc069_w4</th>\n",
       "      <th>dc070_w4</th>\n",
       "      <th>versionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12491</th>\n",
       "      <td>330404313002</td>\n",
       "      <td>3304043130</td>\n",
       "      <td>3304043</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>2 Local Dialect</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11029</th>\n",
       "      <td>326359310001</td>\n",
       "      <td>3263593100</td>\n",
       "      <td>3263593</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>97 Not Assessed</td>\n",
       "      <td>97 Not Assessed</td>\n",
       "      <td>5 Error</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>97 Not Assessed</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>2 Local Dialect</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18516</th>\n",
       "      <td>288251235002</td>\n",
       "      <td>2882512350</td>\n",
       "      <td>2882512</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>5 Error</td>\n",
       "      <td>5 Error</td>\n",
       "      <td>5 Error</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>1 Mandarin</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16475</th>\n",
       "      <td>057633321001</td>\n",
       "      <td>0576333210</td>\n",
       "      <td>0576333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16126</th>\n",
       "      <td>051606203002</td>\n",
       "      <td>0516062030</td>\n",
       "      <td>0516062</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>5 Error</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>2 Local Dialect</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19168</th>\n",
       "      <td>134902227002</td>\n",
       "      <td>1349022270</td>\n",
       "      <td>1349022</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>2 Local Dialect</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13487</th>\n",
       "      <td>154628315001</td>\n",
       "      <td>1546283150</td>\n",
       "      <td>1546283</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>5 Error</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>2 Local Dialect</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7645</th>\n",
       "      <td>075376122001</td>\n",
       "      <td>0753761220</td>\n",
       "      <td>0753761</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>960</th>\n",
       "      <td>064604131001</td>\n",
       "      <td>0646041310</td>\n",
       "      <td>0646041</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>1 Mandarin</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18686</th>\n",
       "      <td>285502121001</td>\n",
       "      <td>2855021210</td>\n",
       "      <td>2855021</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>1 Correct</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>0 No</td>\n",
       "      <td>8 None of the above</td>\n",
       "      <td>2 Local Dialect</td>\n",
       "      <td>2 No</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 219 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ID householdID communityID   dc001_w4         dc002_w4  \\\n",
       "12491  330404313002  3304043130     3304043  1 Correct        1 Correct   \n",
       "11029  326359310001  3263593100     3263593  1 Correct  97 Not Assessed   \n",
       "18516  288251235002  2882512350     2882512  1 Correct        1 Correct   \n",
       "16475  057633321001  0576333210     0576333        NaN              NaN   \n",
       "16126  051606203002  0516062030     0516062  1 Correct        1 Correct   \n",
       "19168  134902227002  1349022270     1349022  1 Correct        1 Correct   \n",
       "13487  154628315001  1546283150     1546283  1 Correct        1 Correct   \n",
       "7645   075376122001  0753761220     0753761        NaN              NaN   \n",
       "960    064604131001  0646041310     0646041  1 Correct        1 Correct   \n",
       "18686  285502121001  2855021210     2855021  1 Correct        1 Correct   \n",
       "\n",
       "              dc003_w4   dc005_w4   dc006_w4   dc007_w4         dc008_w4  ...  \\\n",
       "12491        1 Correct  1 Correct  1 Correct  1 Correct        1 Correct  ...   \n",
       "11029  97 Not Assessed    5 Error  1 Correct  1 Correct  97 Not Assessed  ...   \n",
       "18516          5 Error    5 Error    5 Error        NaN              NaN  ...   \n",
       "16475              NaN        NaN        NaN        NaN              NaN  ...   \n",
       "16126          5 Error  1 Correct  1 Correct  1 Correct        1 Correct  ...   \n",
       "19168        1 Correct  1 Correct  1 Correct        NaN              NaN  ...   \n",
       "13487          5 Error  1 Correct  1 Correct        NaN              NaN  ...   \n",
       "7645               NaN        NaN        NaN        NaN              NaN  ...   \n",
       "960          1 Correct  1 Correct  1 Correct        NaN              NaN  ...   \n",
       "18686        1 Correct  1 Correct  1 Correct        NaN              NaN  ...   \n",
       "\n",
       "      dc068_w4_s2 dc068_w4_s3 dc068_w4_s4 dc068_w4_s5 dc068_w4_s6 dc068_w4_s7  \\\n",
       "12491        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "11029        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "18516        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "16475         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "16126        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "19168        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "13487        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "7645          NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "960          0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "18686        0 No        0 No        0 No        0 No        0 No        0 No   \n",
       "\n",
       "               dc068_w4_s8         dc069_w4 dc070_w4 versionID  \n",
       "12491  8 None of the above  2 Local Dialect     2 No  20200914  \n",
       "11029  8 None of the above  2 Local Dialect     2 No  20200914  \n",
       "18516  8 None of the above       1 Mandarin     2 No  20200914  \n",
       "16475                  NaN              NaN      NaN  20200914  \n",
       "16126  8 None of the above  2 Local Dialect     2 No  20200914  \n",
       "19168  8 None of the above  2 Local Dialect     2 No  20200914  \n",
       "13487  8 None of the above  2 Local Dialect     2 No  20200914  \n",
       "7645                   NaN              NaN      NaN  20200914  \n",
       "960    8 None of the above       1 Mandarin     2 No  20200914  \n",
       "18686  8 None of the above  2 Local Dialect     2 No  20200914  \n",
       "\n",
       "[10 rows x 219 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取文件\n",
    "file_path='C:/Users/Alex/Desktop/python_work/.machine learning/CHARLS2018/Cognition.dta'\n",
    "data1=pd.read_stata(file_path)\n",
    "\n",
    "print(data1.info())\n",
    "data1.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step2** 读取第2个数据包,并展示10个样本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 19816 entries, 0 to 19815\n",
      "Columns: 110 entries, ID to versionID\n",
      "dtypes: category(64), float32(6), float64(35), object(5)\n",
      "memory usage: 7.9+ MB\n",
      "None\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>householdID</th>\n",
       "      <th>communityID</th>\n",
       "      <th>ba000_w2_3</th>\n",
       "      <th>ba001</th>\n",
       "      <th>ba004_w3</th>\n",
       "      <th>ba004_w3_1</th>\n",
       "      <th>ba004_w3_2</th>\n",
       "      <th>ba004_w3_3</th>\n",
       "      <th>ba005_w4</th>\n",
       "      <th>...</th>\n",
       "      <th>bf008</th>\n",
       "      <th>xrtype</th>\n",
       "      <th>xrgender</th>\n",
       "      <th>zfrgender</th>\n",
       "      <th>zfrzodiac</th>\n",
       "      <th>zfrbirth</th>\n",
       "      <th>ziwtime</th>\n",
       "      <th>zbc004</th>\n",
       "      <th>zfredu</th>\n",
       "      <th>versionID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13424</th>\n",
       "      <td>158278323001</td>\n",
       "      <td>1582783230</td>\n",
       "      <td>1582783</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>8 Goat</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1955.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>2 a Few Times</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年08月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13557</th>\n",
       "      <td>154628317001</td>\n",
       "      <td>1546283170</td>\n",
       "      <td>1546283</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>9 Monkey</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年08月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>060440136001</td>\n",
       "      <td>0604401360</td>\n",
       "      <td>0604401</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>10 Rooster</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>2 a Few Times</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年07月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11881</th>\n",
       "      <td>274951202001</td>\n",
       "      <td>2749512020</td>\n",
       "      <td>2749512</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>10 Rooster</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1945.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年07月</td>\n",
       "      <td>2 Non-agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8793</th>\n",
       "      <td>295331213001</td>\n",
       "      <td>2953312130</td>\n",
       "      <td>2953312</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>4 Rabbit</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1951.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年07月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10060</th>\n",
       "      <td>324045211002</td>\n",
       "      <td>3240452110</td>\n",
       "      <td>3240452</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>4 Rabbit</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1963.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年07月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18324</th>\n",
       "      <td>281116129002</td>\n",
       "      <td>2811161290</td>\n",
       "      <td>2811161</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>9 Monkey</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>2 a Few Times</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>2 Female</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年08月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18572</th>\n",
       "      <td>288251239002</td>\n",
       "      <td>2882512390</td>\n",
       "      <td>2882512</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>3 Tiger</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年07月</td>\n",
       "      <td>1 Agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17876</th>\n",
       "      <td>010206103002</td>\n",
       "      <td>0102061030</td>\n",
       "      <td>0102061</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>11 Dog</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1946.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年08月</td>\n",
       "      <td>2 Non-agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14934</th>\n",
       "      <td>122490107001</td>\n",
       "      <td>1224901070</td>\n",
       "      <td>1224901</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>7 Horse</td>\n",
       "      <td>1 Year Month Day</td>\n",
       "      <td>1954.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1 Same</td>\n",
       "      <td>...</td>\n",
       "      <td>1 Never</td>\n",
       "      <td>1 RE Interview</td>\n",
       "      <td>1 Male</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2015年08月</td>\n",
       "      <td>2 Non-agricultural Hukou</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20200914</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 110 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ID householdID communityID ba000_w2_3       ba001  \\\n",
       "13424  158278323001  1582783230     1582783   2 Female      8 Goat   \n",
       "13557  154628317001  1546283170     1546283   2 Female    9 Monkey   \n",
       "615    060440136001  0604401360     0604401     1 Male  10 Rooster   \n",
       "11881  274951202001  2749512020     2749512   2 Female  10 Rooster   \n",
       "8793   295331213001  2953312130     2953312     1 Male    4 Rabbit   \n",
       "10060  324045211002  3240452110     3240452   2 Female    4 Rabbit   \n",
       "18324  281116129002  2811161290     2811161   2 Female    9 Monkey   \n",
       "18572  288251239002  2882512390     2882512     1 Male     3 Tiger   \n",
       "17876  010206103002  0102061030     0102061     1 Male      11 Dog   \n",
       "14934  122490107001  1224901070     1224901     1 Male     7 Horse   \n",
       "\n",
       "               ba004_w3  ba004_w3_1  ba004_w3_2  ba004_w3_3 ba005_w4  ...  \\\n",
       "13424  1 Year Month Day      1955.0         5.0        25.0   1 Same  ...   \n",
       "13557  1 Year Month Day      1968.0        10.0        17.0   1 Same  ...   \n",
       "615    1 Year Month Day      1964.0         4.0        22.0   1 Same  ...   \n",
       "11881  1 Year Month Day      1945.0         7.0        19.0   1 Same  ...   \n",
       "8793   1 Year Month Day      1951.0         1.0        30.0   1 Same  ...   \n",
       "10060  1 Year Month Day      1963.0         9.0        13.0   1 Same  ...   \n",
       "18324  1 Year Month Day      1968.0        12.0        30.0   1 Same  ...   \n",
       "18572  1 Year Month Day      1962.0         5.0        22.0   1 Same  ...   \n",
       "17876  1 Year Month Day      1946.0         3.0        20.0   1 Same  ...   \n",
       "14934  1 Year Month Day      1954.0         6.0        26.0   1 Same  ...   \n",
       "\n",
       "               bf008          xrtype  xrgender zfrgender zfrzodiac zfrbirth  \\\n",
       "13424  2 a Few Times  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
       "13557        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
       "615    2 a Few Times  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
       "11881        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
       "8793         1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
       "10060        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
       "18324  2 a Few Times  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
       "18572        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
       "17876        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
       "14934        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
       "\n",
       "        ziwtime                    zbc004 zfredu versionID  \n",
       "13424  2015年08月      1 Agricultural Hukou    1.0  20200914  \n",
       "13557  2015年08月      1 Agricultural Hukou    1.0  20200914  \n",
       "615    2015年07月      1 Agricultural Hukou    1.0  20200914  \n",
       "11881  2015年07月  2 Non-agricultural Hukou    1.0  20200914  \n",
       "8793   2015年07月      1 Agricultural Hukou    1.0  20200914  \n",
       "10060  2015年07月      1 Agricultural Hukou    1.0  20200914  \n",
       "18324  2015年08月      1 Agricultural Hukou    1.0  20200914  \n",
       "18572  2015年07月      1 Agricultural Hukou    1.0  20200914  \n",
       "17876  2015年08月  2 Non-agricultural Hukou    1.0  20200914  \n",
       "14934  2015年08月  2 Non-agricultural Hukou    1.0  20200914  \n",
       "\n",
       "[10 rows x 110 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取文件\n",
    "file_path='C:/Users/Alex/Desktop/python_work/.machine learning/CHARLS2018/Demographic_Background.dta'\n",
    "data2=pd.read_stata(file_path)\n",
    "\n",
    "print(data2.info())\n",
    "data2.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "**Step3**. 对两个文件包进行合并，并查看合并后的结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "19739  294099304001    2940993040       2940993  1 Correct  1 Correct   \n",
      "19740  294099303002    2940993030       2940993  1 Correct  1 Correct   \n",
      "19741  294099303001    2940993030       2940993  1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993  1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993  1 Correct    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4  ...    bf008  \\\n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...  1 Never   \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...  1 Never   \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...  1 Never   \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...  1 Never   \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...  1 Never   \n",
      "...          ...        ...        ...        ...        ...  ...      ...   \n",
      "19739    5 Error  1 Correct  1 Correct        NaN        NaN  ...  1 Never   \n",
      "19740  1 Correct  1 Correct  1 Correct        NaN        NaN  ...  1 Never   \n",
      "19741  1 Correct  1 Correct  1 Correct        NaN        NaN  ...  1 Never   \n",
      "19742  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...  1 Never   \n",
      "19743    5 Error    5 Error  1 Correct    5 Error  1 Correct  ...  1 Never   \n",
      "\n",
      "               xrtype  xrgender zfrgender zfrzodiac zfrbirth   ziwtime  \\\n",
      "0      1 RE Interview  2 Female       1.0       1.0      1.0  2015年07月   \n",
      "1      1 RE Interview  2 Female       1.0       1.0      1.0  2015年08月   \n",
      "2      1 RE Interview  2 Female       1.0       1.0      1.0  2015年08月   \n",
      "3      1 RE Interview    1 Male       1.0       1.0      1.0  2015年08月   \n",
      "4      1 RE Interview    1 Male       1.0       1.0      1.0  2015年08月   \n",
      "...               ...       ...       ...       ...      ...       ...   \n",
      "19739  1 RE Interview    1 Male       1.0       1.0      1.0  2015年08月   \n",
      "19740  1 RE Interview    1 Male       1.0       1.0      1.0  2015年07月   \n",
      "19741  1 RE Interview  2 Female       1.0       1.0      1.0  2015年07月   \n",
      "19742  1 RE Interview    1 Male       1.0       1.0      1.0  2015年07月   \n",
      "19743  1 RE Interview  2 Female       1.0       1.0      1.0  2015年07月   \n",
      "\n",
      "                         zbc004 zfredu versionID_y  \n",
      "0      2 Non-agricultural Hukou    1.0    20200914  \n",
      "1      2 Non-agricultural Hukou    1.0    20200914  \n",
      "2      2 Non-agricultural Hukou    1.0    20200914  \n",
      "3          1 Agricultural Hukou    1.0    20200914  \n",
      "4      2 Non-agricultural Hukou    1.0    20200914  \n",
      "...                         ...    ...         ...  \n",
      "19739      1 Agricultural Hukou    1.0    20200914  \n",
      "19740      1 Agricultural Hukou    1.0    20200914  \n",
      "19741      1 Agricultural Hukou    1.0    20200914  \n",
      "19742      1 Agricultural Hukou    1.0    20200914  \n",
      "19743      1 Agricultural Hukou    1.0    20200914  \n",
      "\n",
      "[19744 rows x 328 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取两个文件\n",
    "file_path='C:/Users/Alex/Desktop/python_work/.machine learning/CHARLS2018/Cognition.dta'\n",
    "data1=pd.read_stata(file_path)\n",
    "\n",
    "file_path='C:/Users/Alex/Desktop/python_work/.machine learning/CHARLS2018/Demographic_Background.dta'\n",
    "data2=pd.read_stata(file_path)\n",
    "\n",
    "# 根据 'ID' 列进行合并\n",
    "merged_df = pd.merge(data1, data2, on='ID', how='inner')\n",
    "\n",
    "# 查看合并后的结果\n",
    "print(merged_df)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "**Step4**. 对Merged_DF进行计算每行缺失的值，并剔除缺失50%数值的行，生成新DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed rows: Int64Index([    6,     8,     9,    15,    18,    19,    20,    26,    30,\n",
      "               31,\n",
      "            ...\n",
      "            19732, 19733, 19734, 19735, 19736, 19737, 19738, 19739, 19740,\n",
      "            19741],\n",
      "           dtype='int64', length=11533)\n",
      "Cleaned DataFrame:\n",
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "19721  294099316001    2940993160       2940993  1 Correct  1 Correct   \n",
      "19722  294099319001    2940993190       2940993  1 Correct  1 Correct   \n",
      "19723  294099313002    2940993130       2940993  1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993  1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993  1 Correct    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4  ...  \\\n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "...          ...        ...        ...        ...        ...  ...   \n",
      "19721  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19722  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19723    5 Error    5 Error  1 Correct  1 Correct  1 Correct  ...   \n",
      "19742  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19743    5 Error    5 Error  1 Correct    5 Error  1 Correct  ...   \n",
      "\n",
      "               bf008          xrtype  xrgender zfrgender zfrzodiac zfrbirth  \\\n",
      "0            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "1            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "2            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "3            1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "4            1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "...              ...             ...       ...       ...       ...      ...   \n",
      "19721        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "19722        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19723  2 a Few Times  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19742        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19743        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "\n",
      "        ziwtime                    zbc004 zfredu versionID_y  \n",
      "0      2015年07月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "1      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "2      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "3      2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "4      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "...         ...                       ...    ...         ...  \n",
      "19721  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "19722  2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "19723  2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "19742  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "19743  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "\n",
      "[8211 rows x 328 columns]\n"
     ]
    }
   ],
   "source": [
    "# 计算每行缺失值的比例\n",
    "missing_ratio_per_row = merged_df.isnull().sum(axis=1) / merged_df.shape[1]\n",
    "\n",
    "# 筛选出缺失值比例超过 50% 的行\n",
    "rows_to_drop = missing_ratio_per_row[missing_ratio_per_row > 0.5].index\n",
    "\n",
    "# 去除这些行\n",
    "data_c2 = merged_df.drop(index=rows_to_drop)\n",
    "\n",
    "# 打印结果\n",
    "print(\"Removed rows:\", rows_to_drop)\n",
    "print(\"Cleaned DataFrame:\")\n",
    "print(data_c2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step5**. \n",
    "查找相应的列名，替换成可识别的中文。并展示前后差别"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始 DataFrame:\n",
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "19721  294099316001    2940993160       2940993  1 Correct  1 Correct   \n",
      "19722  294099319001    2940993190       2940993  1 Correct  1 Correct   \n",
      "19723  294099313002    2940993130       2940993  1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993  1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993  1 Correct    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4  ...  \\\n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "...          ...        ...        ...        ...        ...  ...   \n",
      "19721  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19722  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19723    5 Error    5 Error  1 Correct  1 Correct  1 Correct  ...   \n",
      "19742  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19743    5 Error    5 Error  1 Correct    5 Error  1 Correct  ...   \n",
      "\n",
      "               bf008          xrtype  xrgender zfrgender zfrzodiac zfrbirth  \\\n",
      "0            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "1            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "2            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "3            1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "4            1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "...              ...             ...       ...       ...       ...      ...   \n",
      "19721        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "19722        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19723  2 a Few Times  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19742        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19743        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "\n",
      "        ziwtime                    zbc004 zfredu versionID_y  \n",
      "0      2015年07月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "1      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "2      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "3      2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "4      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "...         ...                       ...    ...         ...  \n",
      "19721  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "19722  2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "19723  2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "19742  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "19743  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "\n",
      "[8211 rows x 328 columns]\n",
      "修改后的 DataFrame:\n",
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "19721  294099316001    2940993160       2940993  1 Correct  1 Correct   \n",
      "19722  294099319001    2940993190       2940993  1 Correct  1 Correct   \n",
      "19723  294099313002    2940993130       2940993  1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993  1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993  1 Correct    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4  ...  \\\n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "...          ...        ...        ...        ...        ...  ...   \n",
      "19721  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19722  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19723    5 Error    5 Error  1 Correct  1 Correct  1 Correct  ...   \n",
      "19742  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  ...   \n",
      "19743    5 Error    5 Error  1 Correct    5 Error  1 Correct  ...   \n",
      "\n",
      "               bf008          xrtype  xrgender zfrgender zfrzodiac zfrbirth  \\\n",
      "0            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "1            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "2            1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "3            1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "4            1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "...              ...             ...       ...       ...       ...      ...   \n",
      "19721        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "19722        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19723  2 a Few Times  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19742        1 Never  1 RE Interview    1 Male       1.0       1.0      1.0   \n",
      "19743        1 Never  1 RE Interview  2 Female       1.0       1.0      1.0   \n",
      "\n",
      "        ziwtime                        户口 zfredu versionID_y  \n",
      "0      2015年07月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "1      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "2      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "3      2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "4      2015年08月  2 Non-agricultural Hukou    1.0    20200914  \n",
      "...         ...                       ...    ...         ...  \n",
      "19721  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "19722  2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "19723  2015年08月      1 Agricultural Hukou    1.0    20200914  \n",
      "19742  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "19743  2015年07月      1 Agricultural Hukou    1.0    20200914  \n",
      "\n",
      "[8211 rows x 328 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 打印原始 DataFrame\n",
    "print(\"原始 DataFrame:\")\n",
    "print(data_c2)\n",
    "\n",
    "# 使用 rename() 方法一次性修改多个列名\n",
    "data_c3 = data_c2.rename(columns={'ba000_w2_3': '性别', 'ba002_1': '生日', 'bb000_w3_1': '住址类型','bb000_w3_2':'住址区域','bd001_w2_4':'教育','bd001_w3_1':'识字','be001':'婚姻','bg001_w4':'民族','zbc004':'户口'})\n",
    "\n",
    "# 打印修改后的 DataFrame\n",
    "print(\"修改后的 DataFrame:\")\n",
    "print(data_c3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step 6** 用iloc提取指定列，生成New_DF。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "19721  294099316001    2940993160       2940993  1 Correct  1 Correct   \n",
      "19722  294099319001    2940993190       2940993  1 Correct  1 Correct   \n",
      "19723  294099313002    2940993130       2940993  1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993  1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993  1 Correct    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4  \n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "...          ...        ...        ...        ...        ...  \n",
      "19721  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "19722  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "19723    5 Error    5 Error  1 Correct  1 Correct  1 Correct  \n",
      "19742  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  \n",
      "19743    5 Error    5 Error  1 Correct    5 Error  1 Correct  \n",
      "\n",
      "[8211 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 使用iloc提取指定列并生成新的DataFrame\n",
    "new_df = data_c3.iloc[:, [i for i in range(10)]]  # 提取第0列和第10列\n",
    "\n",
    "print(new_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step7** 用new_DF诊断“简易精神状态”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "19721  294099316001    2940993160       2940993  1 Correct  1 Correct   \n",
      "19722  294099319001    2940993190       2940993  1 Correct  1 Correct   \n",
      "19723  294099313002    2940993130       2940993  1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993  1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993  1 Correct    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4 row_status  \n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "...          ...        ...        ...        ...        ...        ...  \n",
      "19721  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "19722  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "19723    5 Error    5 Error  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "19742  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "19743    5 Error    5 Error  1 Correct    5 Error  1 Correct  1 correct  \n",
      "\n",
      "[8211 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "DF_JS = pd.DataFrame(new_df)\n",
    "\n",
    "# 定义一个函数来判断整行的状态\n",
    "def get_row_status(row):\n",
    "    # 检查第4列到第10列是否有 '5 error'\n",
    "    if '5 error' in row[4:10].values:\n",
    "        return '5 error'\n",
    "    else:\n",
    "        return '1 correct'\n",
    "\n",
    "# 应用函数到每一行，生成新的列 'row_status'\n",
    "DF_JS['row_status'] = DF_JS.apply(get_row_status, axis=1)\n",
    "\n",
    "# 输出结果\n",
    "print(DF_JS)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step8** loc提取相关列，生成个人信息new_df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 ID householdID_x communityID_x   dc001_w4        性别      生日\n",
      "0      094004113002    0940041130       0940041  1 Correct  2 Female  1953.0\n",
      "1      094004111002    0940041110       0940041  1 Correct  2 Female     NaN\n",
      "2      094004111001    0940041110       0940041  1 Correct  2 Female     NaN\n",
      "3      094004112001    0940041120       0940041  1 Correct    1 Male     NaN\n",
      "4      094004118001    0940041180       0940041  1 Correct    1 Male  1952.0\n",
      "...             ...           ...           ...        ...       ...     ...\n",
      "19721  294099316001    2940993160       2940993  1 Correct  2 Female     NaN\n",
      "19722  294099319001    2940993190       2940993  1 Correct    1 Male     NaN\n",
      "19723  294099313002    2940993130       2940993  1 Correct    1 Male     NaN\n",
      "19742  294099311001    2940993110       2940993  1 Correct    1 Male     NaN\n",
      "19743  294099311002    2940993110       2940993  1 Correct  2 Female     NaN\n",
      "\n",
      "[8211 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 使用loc提取指定列并生成新的DataFrame\n",
    "new_df3 = data_c3.loc[:,['ID','householdID_x','communityID_x','dc001_w4','性别','生日']]\n",
    "\n",
    "print(new_df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step9**, 检查生日列的缺失信息\n",
    "\n",
    "***数据缺失较多，需要从dta中继续寻找更多和年龄相关信息，来填充*** ，后一步暂时用中位数填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "“生日”列中缺失信息的个数为: 6581\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 8211 entries, 0 to 19743\n",
      "Data columns (total 6 columns):\n",
      " #   Column         Non-Null Count  Dtype   \n",
      "---  ------         --------------  -----   \n",
      " 0   ID             8211 non-null   object  \n",
      " 1   householdID_x  8211 non-null   object  \n",
      " 2   communityID_x  8211 non-null   object  \n",
      " 3   dc001_w4       8211 non-null   category\n",
      " 4   性别             8211 non-null   category\n",
      " 5   生日             1630 non-null   float64 \n",
      "dtypes: category(2), float64(1), object(3)\n",
      "memory usage: 337.0+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "new_df3 = pd.DataFrame(new_df3)\n",
    "\n",
    "# 检查“生日”列的缺失信息个数\n",
    "missing_count = new_df3['生日'].isnull().sum()\n",
    "\n",
    "# 打印缺失信息\n",
    "print(f\"“生日”列中缺失信息的个数为: {missing_count}\")\n",
    "\n",
    "print(new_df3.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 ID householdID_x communityID_x   dc001_w4        性别      生日\n",
      "0      094004113002    0940041130       0940041  1 Correct  2 Female  1953.0\n",
      "1      094004111002    0940041110       0940041  1 Correct  2 Female  1950.0\n",
      "2      094004111001    0940041110       0940041  1 Correct  2 Female  1950.0\n",
      "3      094004112001    0940041120       0940041  1 Correct    1 Male  1950.0\n",
      "4      094004118001    0940041180       0940041  1 Correct    1 Male  1952.0\n",
      "...             ...           ...           ...        ...       ...     ...\n",
      "19721  294099316001    2940993160       2940993  1 Correct  2 Female  1950.0\n",
      "19722  294099319001    2940993190       2940993  1 Correct    1 Male  1950.0\n",
      "19723  294099313002    2940993130       2940993  1 Correct    1 Male  1950.0\n",
      "19742  294099311001    2940993110       2940993  1 Correct    1 Male  1950.0\n",
      "19743  294099311002    2940993110       2940993  1 Correct  2 Female  1950.0\n",
      "\n",
      "[8211 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "new_df3 = pd.DataFrame(new_df3)\n",
    "\n",
    "# 检查 \"生日\" 列的缺失值\n",
    "missing_values = new_df3['生日'].isnull()\n",
    "\n",
    "# 计算有数值部分的中位数\n",
    "median_value = new_df3.loc[~missing_values, '生日'].median()\n",
    "\n",
    "# 用中位数填充缺失值\n",
    "new_df3['生日'].fillna(median_value, inplace=True)\n",
    "\n",
    "# 打印结果以验证\n",
    "print(new_df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step10**, 拆分new_df,按行数75%切分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "**75% 的 DataFrame:**\n",
      "                 ID householdID_x communityID_x   dc001_w4   dc002_w4  \\\n",
      "0      094004113002    0940041130       0940041  1 Correct  1 Correct   \n",
      "1      094004111002    0940041110       0940041  1 Correct  1 Correct   \n",
      "2      094004111001    0940041110       0940041  1 Correct  1 Correct   \n",
      "3      094004112001    0940041120       0940041  1 Correct  1 Correct   \n",
      "4      094004118001    0940041180       0940041  1 Correct  1 Correct   \n",
      "...             ...           ...           ...        ...        ...   \n",
      "14496  242702115002    2427021150       2427021  1 Correct    5 Error   \n",
      "14497  242702115001    2427021150       2427021  1 Correct  1 Correct   \n",
      "14498  242702114001    2427021140       2427021  1 Correct    5 Error   \n",
      "14499  242702114002    2427021140       2427021  1 Correct    5 Error   \n",
      "14500  242702104001    2427021040       2427021    5 Error    5 Error   \n",
      "\n",
      "        dc003_w4   dc005_w4   dc006_w4   dc007_w4   dc008_w4 row_status  \n",
      "0        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "1      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "2      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "3      1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "4        5 Error  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "...          ...        ...        ...        ...        ...        ...  \n",
      "14496  1 Correct  1 Correct  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "14497  1 Correct    5 Error  1 Correct    5 Error  1 Correct  1 correct  \n",
      "14498  1 Correct    5 Error  1 Correct  1 Correct  1 Correct  1 correct  \n",
      "14499    5 Error    5 Error  1 Correct    5 Error    5 Error  1 correct  \n",
      "14500    5 Error    5 Error  1 Correct    5 Error  1 Correct  1 correct  \n",
      "\n",
      "[6158 rows x 11 columns]\n",
      "**25% 的 DataFrame:**\n",
      "                 ID householdID_x communityID_x         dc001_w4   dc002_w4  \\\n",
      "14501  242702104002    2427021040       2427021          5 Error  1 Correct   \n",
      "14502  242702124001    2427021240       2427021          5 Error    5 Error   \n",
      "14503  242702101001    2427021010       2427021        1 Correct  1 Correct   \n",
      "14508  242702123001    2427021230       2427021        1 Correct    5 Error   \n",
      "14509  242702123002    2427021230       2427021  97 Not Assessed  1 Correct   \n",
      "...             ...           ...           ...              ...        ...   \n",
      "19721  294099316001    2940993160       2940993        1 Correct  1 Correct   \n",
      "19722  294099319001    2940993190       2940993        1 Correct  1 Correct   \n",
      "19723  294099313002    2940993130       2940993        1 Correct  1 Correct   \n",
      "19742  294099311001    2940993110       2940993        1 Correct  1 Correct   \n",
      "19743  294099311002    2940993110       2940993        1 Correct    5 Error   \n",
      "\n",
      "              dc003_w4         dc005_w4   dc006_w4   dc007_w4   dc008_w4  \\\n",
      "14501        1 Correct          5 Error  1 Correct  1 Correct  1 Correct   \n",
      "14502          5 Error          5 Error    5 Error    5 Error    5 Error   \n",
      "14503        1 Correct  97 Not Assessed  1 Correct  1 Correct  1 Correct   \n",
      "14508        1 Correct        1 Correct  1 Correct  1 Correct  1 Correct   \n",
      "14509  97 Not Assessed  97 Not Assessed  1 Correct  1 Correct  1 Correct   \n",
      "...                ...              ...        ...        ...        ...   \n",
      "19721        1 Correct        1 Correct  1 Correct  1 Correct  1 Correct   \n",
      "19722        1 Correct        1 Correct  1 Correct  1 Correct  1 Correct   \n",
      "19723          5 Error          5 Error  1 Correct  1 Correct  1 Correct   \n",
      "19742        1 Correct        1 Correct  1 Correct  1 Correct  1 Correct   \n",
      "19743          5 Error          5 Error  1 Correct    5 Error  1 Correct   \n",
      "\n",
      "      row_status  \n",
      "14501  1 correct  \n",
      "14502  1 correct  \n",
      "14503  1 correct  \n",
      "14508  1 correct  \n",
      "14509  1 correct  \n",
      "...          ...  \n",
      "19721  1 correct  \n",
      "19722  1 correct  \n",
      "19723  1 correct  \n",
      "19742  1 correct  \n",
      "19743  1 correct  \n",
      "\n",
      "[2053 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 计算拆分点\n",
    "row_count = len(new_df)\n",
    "split_point = int(row_count * 0.75)\n",
    "\n",
    "# 按行拆分为 75% 和 25%\n",
    "new_df_75 = new_df.iloc[:split_point]\n",
    "new_df_25 = new_df.iloc[split_point:]\n",
    "\n",
    "# 打印结果\n",
    "print(\"**75% 的 DataFrame:**\")\n",
    "print(new_df_75)\n",
    "\n",
    "\n",
    "print(\"**25% 的 DataFrame:**\")\n",
    "print(new_df_25)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
